﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using RRHH.Entities;
using System.Data.SqlClient;
using System.Data;

namespace RRHH.DataAccess
{
    public class DAOPersonal : IDAOBase<BEPersonal, int>
    {

        public Boolean Update(BEPersonal oPersonal)
        {

            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.Append(" UPDATE PERSONAL SET ");

            sqlBuilder.Append(" NOMBRESEMPLEADO = @NOMBRESEMPLEADO, ");
            sqlBuilder.Append(" APEPATERNOEMPLEADO = @APEPATERNOEMPLEADO, ");
            sqlBuilder.Append(" APEMATERNOEMPLEADO = @APEMATERNOEMPLEADO ");
            sqlBuilder.Append(" WHERE IDPersonal = @IDPersonal ");

            string sql = sqlBuilder.ToString();

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = sql;

            sqlCommand.Parameters.Add("@IDPERSONAL", SqlDbType.Int);
            sqlCommand.Parameters.Add("@NOMBRESEMPLEADO", SqlDbType.VarChar);
            sqlCommand.Parameters.Add("@APEPATERNOEMPLEADO", SqlDbType.VarChar);
            sqlCommand.Parameters.Add("@APEMATERNOEMPLEADO", SqlDbType.VarChar);

            sqlCommand.Parameters["@IDPERSONAL"].Value = oPersonal.IDPERSONAL;
            sqlCommand.Parameters["@NOMBRESEMPLEADO"].Value = oPersonal.NOMBRESEMPLEADO;
            sqlCommand.Parameters["@APEPATERNOEMPLEADO"].Value = oPersonal.APEPATERNOEMPLEADO;
            sqlCommand.Parameters["@APEMATERNOEMPLEADO"].Value = oPersonal.APEMATERNOEMPLEADO;

            using (DataAccess adoDataService = new DataAccess())
            {
                adoDataService.BeginTransaction();
                adoDataService.ExecuteNonQuery(sqlCommand);
                adoDataService.CommitTransaction();
                adoDataService.Dispose();

            }

            return true;

        }




        public BEPersonal Create(BEPersonal oPersonal)
        {
            try
            {
                SqlCommand sqlCommand = new SqlCommand("SP_REGISTRAR_PERSONAL");
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.Parameters.Add(new SqlParameter("@NOMBRESEMPLEADO", oPersonal.NOMBRESEMPLEADO));
                sqlCommand.Parameters.Add(new SqlParameter("@APEPATERNOEMPLEADO", oPersonal.APEPATERNOEMPLEADO));
                sqlCommand.Parameters.Add(new SqlParameter("@APEMATERNOEMPLEADO", oPersonal.APEMATERNOEMPLEADO));
                sqlCommand.Parameters.Add(new SqlParameter("@FECHANACIMIENTO", oPersonal.FECHANACIMIENTO));
                sqlCommand.Parameters.Add(new SqlParameter("@DIRECCIONEMPLEADO", oPersonal.DIRECCIONEMPLEADO));
                sqlCommand.Parameters.Add(new SqlParameter("@IDESTADOCIVIL", oPersonal.IDESTADOCIVIL));
                sqlCommand.Parameters.Add(new SqlParameter("@FLAGSEXO", oPersonal.FLAGSEXO));
                sqlCommand.Parameters.Add(new SqlParameter("@IDDEPDOMICILIO", oPersonal.IDDEPDOMICILIO));
                sqlCommand.Parameters.Add(new SqlParameter("@IDPROVDOMICILIO", oPersonal.IDPROVDOMICILIO));
                sqlCommand.Parameters.Add(new SqlParameter("@IDDISTDOMICILIO", oPersonal.IDDISTDOMICILIO));
                sqlCommand.Parameters.Add(new SqlParameter("@EMAIL", oPersonal.EMAIL));
                sqlCommand.Parameters.Add(new SqlParameter("@IDDOCIDENTIDAD", oPersonal.IDDOCIDENTIDAD));
                sqlCommand.Parameters.Add(new SqlParameter("@NUMERODOCUMENTO", oPersonal.NUMERODOCUMENTO));
                sqlCommand.Parameters.Add(new SqlParameter("@FLAGESTADO", oPersonal.FLAGESTADO));

                using (DataAccess adoDataService = new DataAccess())
                {
                    adoDataService.BeginTransaction();
                    Int32 orderID = adoDataService.ExecuteScalar(sqlCommand);
                    adoDataService.CommitTransaction();
                    adoDataService.Dispose();

                    oPersonal.IDPERSONAL = orderID;
                }

            }
            catch (Exception ex)
            {

            }

            return oPersonal;

        }


        public BEPersonal Get(int personalID)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.Append(" SELECT * FROM PERSONAL  ");
            sqlBuilder.Append(" WHERE IDPersonal = @IDPersonal ");

            string sql = sqlBuilder.ToString();

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Parameters.Add("@IDPERSONAL", SqlDbType.Int);
            sqlCommand.Parameters["@IDPERSONAL"].Value = personalID;

            sqlCommand.CommandText = sql;

            using (DataAccess adoDataService = new DataAccess())
            {
                BEPersonal oPersonal = new BEPersonal();
                IDataReader dr = adoDataService.ExecuteReader(sqlCommand);

                if (dr.Read())
                {
                    oPersonal.IDPERSONAL = Convert.ToInt32(dr["IDPERSONAL"]);
                    oPersonal.NOMBRESEMPLEADO = Convert.ToString(dr["NOMBRESEMPLEADO"]);
                    oPersonal.APEPATERNOEMPLEADO = Convert.ToString(dr["APEPATERNOEMPLEADO"]);
                    oPersonal.APEMATERNOEMPLEADO = Convert.ToString(dr["APEMATERNOEMPLEADO"]);
                }
                dr.Close();

                adoDataService.Dispose();

                return oPersonal;
            }

        }

        public bool Delete(int personalID)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.Append(" DELETE FROM PERSONAL  ");
            sqlBuilder.Append(" WHERE IDPERSONAL = @IDPERSONAL ");

            string sql = sqlBuilder.ToString();

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Parameters.Add("@IDPERSONAL", SqlDbType.Int);
            sqlCommand.Parameters["@IDPERSONAL"].Value = personalID;

            sqlCommand.CommandText = sql;

            using (DataAccess adoDataService = new DataAccess())
            {
                adoDataService.BeginTransaction();
                adoDataService.ExecuteNonQuery(sqlCommand);
                adoDataService.CommitTransaction();
                adoDataService.Dispose();

            }

            return true;

        }

        public List<BEPersonal> List()
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.Append(" SELECT * FROM PERSONAL ");

            string sql = sqlBuilder.ToString();

            SqlCommand sqlCommand = new SqlCommand();

            sqlCommand.CommandText = sql;

            using (DataAccess adoDataService = new DataAccess())
            {

                List<BEPersonal> oPersonalList = new List<BEPersonal>();
                IDataReader dr = adoDataService.ExecuteReader(sqlCommand);

                while (dr.Read())
                {
                    BEPersonal oPersonal = new BEPersonal();
                    oPersonal.IDPERSONAL = Convert.ToInt32(dr["IDPERSONAL"]);
                    oPersonal.NOMBRESEMPLEADO = Convert.ToString(dr["NOMBRESEMPLEADO"]);
                    oPersonal.APEPATERNOEMPLEADO = Convert.ToString(dr["APEPATERNOEMPLEADO"]);
                    oPersonal.APEMATERNOEMPLEADO = Convert.ToString(dr["APEMATERNOEMPLEADO"]);

                    oPersonalList.Add(oPersonal);
                }
                dr.Close();

                adoDataService.Dispose();

                return oPersonalList;
            }

        }

        public BEPersonal Obtener_x_ID(int personalID)
        {

            string sql = "SP_OBTENER_EMPLEADO_X_IDPERSONAL";

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Parameters.Add("@IDPERSONAL", SqlDbType.Int).Value = personalID;
            sqlCommand.CommandText = sql;
            sqlCommand.CommandType = CommandType.StoredProcedure;

            using (DataAccess adoDataService = new DataAccess())
            {
                BEPersonal oPersonal = new BEPersonal();
                IDataReader dr = adoDataService.ExecuteReader(sqlCommand);

                if (dr.Read())
                {

                    oPersonal.DATOSCOMPLETOS = Convert.ToString(dr["DATOSCOMPLETOS"]);
              
                }
                dr.Close();

                adoDataService.Dispose();

                return oPersonal;
            }

        }
    }
}